#database demo
import sys
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel
from PyQt5.QtCore import *
from PyQt5.QtWidgets import *
class MainWindow(QWidget):
    def __init__(self, parent=None):
        super(MainWindow, self).__init__(parent)
        self.db = QSqlDatabase.addDatabase("QSQLITE")
        self.db.setDatabaseName("wizardassistant.db")
        self.db.open()
        self.model = QSqlTableModel()
        self.initializedModel()

        self.tableView = QTableView()

        # turn off row numbers
        self.tableView.verticalHeader().setVisible(False)
        # turn off horizontal headers
        # self.tableView.horizontalHeader().setVisible(False)

        # self.tableView.resizeColumnsToContents()
        # self.tableView.resizeRowsToContents()

        self.tableView.horizontalHeader().setDefaultAlignment(Qt.AlignLeft)
        self.source_model = self.model
        # self.initializedModel()
        self.proxy_model = QSortFilterProxyModel(self.source_model)
        self.searchcommands = QLineEdit("")
        self.searchcommands.setObjectName(u"searchcommands")
        self.searchcommands.setAlignment(Qt.AlignLeft)
        self.proxy_model.setSourceModel(self.source_model)
        self.tableView.setModel(self.proxy_model)

        # hide columns from the maintableview
        self.tableView.hideColumn(0)
        self.tableView.hideColumn(3)
        self.tableView.hideColumn(4)
        self.tableView.hideColumn(5)
        self.tableView.hideColumn(6)
        self.tableView.hideColumn(7)
        self.proxy_model.setFilterRegExp(QRegExp(self.searchcommands.text(), Qt.CaseInsensitive,
                                                 QRegExp.FixedString))
        # search all columns
        self.proxy_model.setFilterKeyColumn(-1)

        # enable sorting by columns
        self.tableView.setSortingEnabled(True)

        # set editing disabled for my use
        self.tableView.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.tableView.setWordWrap(True)
        self.layout = QVBoxLayout()
        self.command_description = QLabel()
        self.command_description.setWordWrap(True)
        self.command_requires_label = QLabel("Command Requires:")
        self.command_requires_label.setWordWrap(True)
        hLayout = QHBoxLayout()
        hLayout.addWidget(self.command_requires_label)
        self.layout.addWidget(self.tableView)
        self.layout.addWidget(self.searchcommands)
        self.layout.addWidget(self.command_description)
        self.layout.addLayout(hLayout)
        self.setLayout(self.layout)
        self.resize(400, 600)

        self.searchcommands.textChanged.connect(self.searchcommands.update)
        self.searchcommands.textChanged.connect(self.proxy_model.setFilterRegExp)
        self.searchcommands.setText("")
        self.searchcommands.setPlaceholderText(u"search command")
        self.tableView.clicked.connect(self.listclicked)

    def initializedModel(self):
        self.model.setTable("commands")
        # self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
        self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
        self.model.select()
        self.model.setHeaderData(0, Qt.Horizontal, "ID")
        self.model.setHeaderData(1, Qt.Horizontal, "Category")
        self.model.setHeaderData(2, Qt.Horizontal, "command_alias")
        self.model.setHeaderData(3, Qt.Horizontal, "command")
        self.model.setHeaderData(4, Qt.Horizontal, "requires")
        self.model.setHeaderData(5, Qt.Horizontal, "description")
        self.model.setHeaderData(6, Qt.Horizontal, "controlpanel")
        self.model.setHeaderData(7, Qt.Horizontal, "verification")

    def onAddRow(self):
        self.model.insertRows(self.model.rowCount(), 1)
        self.model.submit()

    def onDeleteRow(self):
        self.model.removeRow(self.tableView.currentIndex().row())
        self.model.submit()
        self.model.select()

    def closeEvent(self, event):
        self.db.close()

    def listclicked(self, index):
        row = index.row()
        cmd = self.proxy_model.data(self.proxy_model.index(row, 3))
        cmd_requires = self.proxy_model.data(self.proxy_model.index(row, 4))
        cmd_description = self.proxy_model.data(self.proxy_model.index(row, 5))
        print(cmd_description)
        self.command_description.setText(cmd_description)
        self.command_requires_label.setText('This command requires being executed via: ' + cmd_requires.upper())

if __name__ == "__main__":
    app = QApplication(sys.argv)
    window = MainWindow()
    window.show()
    sys.exit(app.exec_())